﻿using Common;
using DBUtility;
using Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BaseDal
{
    public class ItemDal
    {
        private static readonly Lazy<ItemDal> dal = new Lazy<ItemDal>(() => new ItemDal());
        public static ItemDal Instance { get; } = dal.Value;

        private ItemDal()
        { }

        private const string table1 = "Item";
        private const string table2 = "ItemType";

        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public bool IsExist(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + table1);
            strSql.Append(" where IsDelete=0 " + strWhere);
            return SqlHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 查询SQL语句
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public StringBuilder GetSelectSql(string strWhere = "")
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT t1.ID,t1.ItemID,t1.ItemName,t1.CardID,t1.Specs,t1.Price,t1.Unit,t1.TypeID,t2.TypeName,");
            sb.Append("t1.InvUpper,t1.InvLower,t1.Status ");
            sb.Append("FROM " + table1 + " t1 ");
            sb.Append("Left join " + table2 + " t2 on t1.TypeID=t2.ID ");
            sb.Append(" Where t1.IsDelete=0 ");

            if (!string.IsNullOrEmpty(strWhere))
            {
                sb.Append(strWhere);
            }

            return sb;
        }

        /// <summary>
        /// 查询分页数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetPageList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            string countSql = PageHelper.CreateCountingSql(sb.ToString());
            recordCount = Convert.ToInt32(SqlHelper.GetSingle(countSql));
            string pageSql = PageHelper.CreatePageSql(recordCount, pageSize, pageIndex, sb.ToString(), filedOrder);
            DataSet ds = SqlHelper.Query(pageSql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public List<Item> GetList(string strWhere)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            DataSet ds = SqlHelper.Query(sb.ToString());
            return CommonOperation.ConvertDataTableToModelList<Item>(ds.Tables[0]);
        }

        /// <summary>
        /// 根据id查询实体类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Item GetModelById(int id)
        {
            StringBuilder sb = GetSelectSql("and t1.ID=@id");
            SqlParameter[] parameters =
            {
                new SqlParameter("@id",id)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return CommonOperation.DataTableToModel<Item>(ds.Tables[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 新增物品
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddItem(Item model)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO " + table1 + "(");
            sb.Append("ItemID,ItemName,CardID,Specs,Price,Unit,TypeID,");
            sb.Append("InvUpper,InvLower)");
            sb.Append(" VALUES(");
            sb.Append("@itemID,@itemName,@cardID,@specs,@price,@unit,@typeID,");
            sb.Append("@invUpper,@InvLower) ");
            sb.Append("SELECT  @@IDENTITY  as  'id'");
            SqlParameter[] parameters =
           {
                new SqlParameter("@itemID",model.ItemID),
                new SqlParameter("@itemName",model.ItemName),
                new SqlParameter("@cardID",model.CardID),
                new SqlParameter("@specs",model.Specs),
                new SqlParameter("@price",model.Price),
                new SqlParameter("@unit",model.Unit),
                new SqlParameter("@typeID",model.TypeID),
                new SqlParameter("@invUpper",model.InvUpper),
                new SqlParameter("@InvLower",model.InvLower)
            };
            object result = SqlHelper.ExecuteReturnIdentity(sb.ToString(), parameters);
            if (int.TryParse(result.ToString(), out int id))
            {
                model.ID = id;
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 修改物品
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateItem(Item model)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Update " + table1 + " SET ");
            sb.Append("ItemName=@itemName,");
            sb.Append("Specs=@specs,");
            sb.Append("Price=@price,");
            sb.Append("Unit=@unit,");
            sb.Append("InvUpper=@invUpper,");
            sb.Append("InvLower=@invLower ");
            sb.Append("WHERE ID=@id");
            SqlParameter[] parameters = {
                new SqlParameter("@itemName",model.ItemName),
                new SqlParameter("@specs",model.Specs),
                new SqlParameter("@price",model.Price),
                new SqlParameter("@unit",model.Unit),
                new SqlParameter("@invUpper",model.InvUpper),
                new SqlParameter("@invLower",model.InvLower),
                new SqlParameter("@id",model.ID)
            };
            int result = SqlHelper.ExecuteSql(sb.ToString(), parameters);
            return result > 0;
        }

        /// <summary>
        /// 删除物品
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public bool DeleteItem(string ids)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" update " + table1 + " set ");
            strSql.Append(" IsDelete=1 ");
            strSql.Append(" Where ID in(" + ids + ")");
            int result = SqlHelper.ExecuteSql(strSql.ToString());
            return result > 0;
        }

        #region --类别管理--

        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public bool IsExistType(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + table2);
            strSql.Append(" where " + strWhere);
            return SqlHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 查询类别SQL语句
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public StringBuilder GetTypeSelectSql(string strWhere = "")
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID,ParentId,TypeName,Description ");
            sb.Append("FROM " + table2);

            if (!string.IsNullOrEmpty(strWhere))
            {
                sb.Append(" Where " + strWhere);
            }

            return sb;
        }

        /// <summary>
        /// 查询类别list
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public List<ItemType> GetListTypeModels(string strWhere)
        {
            StringBuilder sb = GetTypeSelectSql(strWhere);
            DataSet ds = SqlHelper.Query(sb.ToString());
            List<ItemType> list = CommonOperation.ConvertDataTableToModelList<ItemType>(ds.Tables[0]);
            return list;
        }

        /// <summary>
        /// 根据类别名称获取实体对象
        /// </summary>
        /// <param name="typeName"></param>
        /// <returns></returns>
        public ItemType GetSingleByTypeName(string typeName)
        {
            StringBuilder sb = GetTypeSelectSql(" TypeName = '" + typeName + "'");
            DataSet ds = SqlHelper.Query(sb.ToString());
            ItemType model = CommonOperation.DataTableToModel<ItemType>(ds.Tables[0]);
            return model;
        }

        /// <summary>
        /// 根据id查询实体类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ItemType GetTypeModelById(int id)
        {
            StringBuilder sb = GetTypeSelectSql(" ID=@id");
            SqlParameter[] parameters =
            {
                new SqlParameter("@id",id)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return CommonOperation.DataTableToModel<ItemType>(ds.Tables[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取某一分类下的所有子分类
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public List<ItemType> GetTypeByName(string typeName)
        {
            // 查询父级分类
            ItemType model = GetSingleByTypeName(typeName);
            if (model == null)
            {
                return null;
            }
            StringBuilder sb = GetTypeSelectSql(" ParentId=" + model.ID);
            DataSet ds = SqlHelper.Query(sb.ToString());
            List<ItemType> list = CommonOperation.ConvertDataTableToModelList<ItemType>(ds.Tables[0]);
            return list;
        }

        /// <summary>
        /// 新增类别
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddItemType(ItemType model)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO " + table2 + "(");
            sb.Append("ParentId,TypeName,Description)");
            sb.Append(" VALUES(");
            sb.Append("@parentId,@typeName,@description)");
            SqlParameter[] parameters =
           {
                new SqlParameter("@parentId",model.ParentId),
                new SqlParameter("@typeName",model.TypeName),
                new SqlParameter("@description",model.Description)
            };
            int res = SqlHelper.ExecuteSql(sb.ToString(), parameters);
            return res > 0;
        }

        /// <summary>
        /// 删除物品类别
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="userName"></param>
        /// <returns></returns>
        public bool DeleteItemType(int id)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Delete from " + table2 + " ");
            sb.Append("WHERE ID=@id");
            SqlParameter[] parameters = {
                new SqlParameter("@id",id),
            };
            int result = SqlHelper.ExecuteSql(sb.ToString(), parameters);
            return result > 0;
        }

        #endregion --类别管理--
    }
}